Create Login and Role Name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create Login and Role Name

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-15 : 17:37:56

Hello,

I am trying to create a SQL Server Security Login to access to a database.
That login will be used by my application Linq to SQL context.

What is the correct rolename for this kind of user. I have the following:

create login WAT
with password = 'wat',
default_database = WAT,
default_language = english;
go
exec sys.sp_addsrvrolemember @loginame = N'WAT', @rolename = N'sysadmin'

use WAT;
go

Should I use sysadmin?
I think this is not correct.

Am I creating the login correctly?

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 17:40:08

I doubt sysadmin is needed. Sysadmin should be reserved for DBA access only. What types of things does the user need to do?

I would think db_owner for the one database would be sufficient.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-15 : 17:50:42

Basically, read and write using Linq, well converted to SQL Queries.

I tried exec sys.sp_addsrvrolemember @loginame = N'WAT', @rolename = N'db_owner'

But I got the error:
Msg 15402, Level 11, State 1, Procedure sp_addsrvrolemember, Line 23
'db_owner' is not a fixed server role.

What is the correct value for db_owner?
Do I need to change something on my T-SQL?

And when I backup the database and restore can I also backup restore the login?

Thanks,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 17:55:19

db_owner is the correct role, however it isn't a server role. It's a database role.

If you just need read/write, then you just need db_datareader and db_datawriter. These are also database roles.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-15 : 18:15:40

Could you please tell me how to create the database role db_owner?

I am using the following to create this database


exec sp_configure 'filestream access level', 2;
reconfigure;
go 

use master
go

if exists (select name from sys.databases where name = N'WAT')
  drop database WAT

create database WAT on primary ( 
  name = 'WAT_Data',
  filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_Data.mdf',
  size = 20MB,
  maxsize = 2GB,
  filegrowth = 10MB 
),
filegroup WAT contains filestream ( 
  name = 'WAT_File',
  filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_File.ndf'
)
log on ( 
  name = 'WAT_Log',
  filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_Log.ldf',
  size = 10MB,
  maxsize = 200MB,
  filegrowth = 5MB 
)
go

if exists (select * from sys.server_principals where name = N'WAT')
drop login WAT
go

create login WAT
with password = 'watpass', 
default_database = WAT,
default_language = english;
go
exec sys.sp_addsrvrolemember @loginame = N'WAT', @rolename = N'db_owner'

use WAT;
go

-- Create database tables

I am using this not just to create the database for the first time but also if I want to recreated from scratch again.

Thank You,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 18:17:30

You need to use the sp_addrolemember system stored procedure to add a user to a database role. sp_addsrvrolemember is only for server roles.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-15 : 18:25:42

Yes,

I tried that before:


create login WAT
with password = 'watpass', 
default_database = WAT,
default_language = english;
go
exec sys.sp_addrolemember @membername = N'WAT', @rolename = N'db_owner'

But I get the error:
User or role 'WAT' does not exist in this database.

Which is strange because I just add it before ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 18:32:57

You actually haven't added it yet. You've only added the login. You need to add the login as a user to the database.

You need to call CREATE USER after CREATE LOGIN, and then call sp_addrolemember in that database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-15 : 21:23:22

I created the following T-SQL that creates the login, the user and the database.

First it checks if there is already a user, login or database and if so deletes all them.

This is because I would like to use this code to create or recreate everything from scratch.

Could you, please, tell me if I am doing this correctly?

-- Define file stream access level
exec sp_configure 'filestream access level', 2;
reconfigure;
go

-- Start connection
use master
go

-- Drop user
if exists (select name from sys.database_principals where name = N'WAT')
drop user WAT
go

-- Drop login
if exists (select * from sys.server_principals where name = N'WAT')
drop login WAT
go

-- Drop database
if exists (select name from sys.databases where name = N'WAT')
drop database WAT

-- Create database, filegroup and log
create database WAT on primary (
name = 'WAT_Data',
filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_Data.mdf',
size = 20MB,
maxsize = 2GB,
filegrowth = 10MB
),
filegroup WAT contains filestream (
name = 'WAT_File',
filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_File.ndf'
)
log on (
name = 'WAT_Log',
filename = 'c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\WAT_Log.ldf',
size = 10MB,
maxsize = 200MB,
filegrowth = 5MB
)
go

-- Create login
create login WAT
with password = 'watpass',
default_database = WAT,
default_language = english;
go

-- Create user
create user WAT;
use WAT;
create user WAT for login WAT;
go

-- Define role
exec sys.sp_addrolemember @membername = N'WAT', @rolename = N'db_owner'

-- Start connection
use WAT;
go

-- Create tables and relationships

Two things I notice:

1 - When I check the login the "Enforce password expiration" is not selected. Should it be?

2 - The login is also mapped to master database as public. Am I missing something?

In relation to WAT is mapped as db_owner and public.

Finally, when I do a backup of this database can I include everything (login, user, etc) and restore it later?

Thank You,

Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-15 : 23:02:59

We don't use the password expiration option. Whether or not you want to use it is up to you. You haven't specified it in your code though.

Mapping to public in master is fine.

Logins are stored in master, so backup/restore of a user database doesn't include that info.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2010-06-16 : 06:05:30

I have just one more question:

Should I create a role with permissions equal to db_owner and then associate that role to the user instead of using db_owner?

Is it more correct?

How can I do that?

Thank You,
Miguel

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-16 : 12:45:59

I don't see why you'd want to do that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources