Create Server authenticated login

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 Server authenticated login

Author

Topic

Jumbuck
Starting Member

11 Posts

Posted - 2009-04-23 : 18:51:04

How do you create UserName and Password access to a database using SQL Server authentication. I want to deploy a database that is accessible to anyone entering the UserName and Password, so I think I need SQL Server authentication.

Microsoft SQL Server Management Studio does not give me any way to add a database user other than as a windows authenticated login to the server on my development computer.

The server's authentication mode is "SQL Server and Widows Authentication Mode".

How does one do it?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-23 : 19:52:10

In Management Studio connect to the server you want to add the login for. Expand the Security folder, right click on Logins and choose New Login. Make sure to select SQL Server Authentication. Hope that helps.

Some days you're the dog, and some days you're the fire hydrant.

Jumbuck
Starting Member

11 Posts

Posted - 2009-04-24 : 01:27:34

I tried creating a login name and password in Management Studio Express with Sql Server authentication and the desired database selected as the default. If I then try to connect to that database outside Management Studio using that login, I get the error message, login failed for user <name>. If I try to open the database in Management Studio Express via Sql Server authentication, the database is listed in the left window in outline only (for example, if I open its Tables folder, none of its tables are listed.

What does this rendering in outline signify? That might provide a clue as to what I'm doing wrong.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-24 : 01:35:57

It doesn't sound like you provided any permissions inside the database.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jumbuck
Starting Member

11 Posts

Posted - 2009-04-24 : 13:20:34

Running SQL Server Management Studio Express as administrator and logging using windows authentication:

- I select from the left pane a datablase "JustForTest.mdf",
- click "New Query" and
- in the Query pane type

GRANT SELECT,EXECUTE ON JustForTest.mdf
TO <name of a Sql Server authenticated login that has JustForTest.mdf as it's default database >

- click "Execute" from the query pane context menu

This yields the following result;

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'mdf', because it does not exist or you do not have permission.

If I delete the mdf extension, and again execute, I get the following error:

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'JustForTest', because it does not exist or you do not have permission.

How can I set permissions for this login on this databse?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-24 : 15:21:12

The GRANT command needs to happen on the objects not on the database.

For example: GRANT SELECT ON SomeUserTable TO SomeUser

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jumbuck
Starting Member

11 Posts

Posted - 2009-04-24 : 16:33:05

So, I tried
GRANT SELECT, EXECUTE ON tblUser TO TestLogin
('TestLogin' is named under in left pane under Security/Users folder of selected database)

I got the following error:
Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
I tried
USE <seleced database name.mdf>
GRANT SELECT, EXECUTE ON tblUser
TO TestLogin
I got the response:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'JustForDelete'. No entry found with that name. Make sure that the name is entered correctly.
Can you see what I'm doing wrong now?
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-24 : 16:41:24

You can't use EXEC on tables. EXEC is for stored procedures and scalar functions.

And you can't use the file name for your database like you have been trying, just use JustForDelete.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Jumbuck
Starting Member

11 Posts

Posted - 2009-04-24 : 19:43:31

Thanks for all your help, but I have one more question if you'll permit.

When using Exec for stored procedures, do you have to name every single stored procedure like you apparently have to do with tables, or is there some generic code that will capture them all?

Thanks Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-25 : 19:01:06

Each object gets its own permissions, but you can easily do this with a script. Here's a script that I use in one of my environments, it forces specific naming conventions so feel free to modify it to whatever you use:


DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	
	(	
		(type = 'P' AND name LIKE 'usp[_]%')
		OR
		(type = 'FN' AND name LIKE 'udf[_]%')
		OR
		(type = 'TF' AND name LIKE 'udf[_]%')
		--OR
		--(type = 'U')
		--OR
		--(type = 'V' AND name LIKE 'v[_]%')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType = 'P' OR @objType = 'FN'
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO SomeUserOrSomeRole')

	IF @objType = 'TF'
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO SomeUserOrSomeRole')

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps
GO

By the way, I haven't used this code in quite some time, but it should work still.

Also, if you want to grant permissions to tables, you'll need to add code to handle that. My systems only get execute on stored procedures. I added code for SELECT on table-valued functions so you can see what that would look like. The script should give you the gist on how to make granting permissions easier.

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

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources