| Author |
Topic |
|
BButler59
Starting Member
7 Posts |
 Posted - 2010-10-07 : 16:54:58
|
| I am new to SQL Server Security and I am trying to set up a user to be able to only execute certain stored procedures... the problem I am having is that I cannot get the user to login unless I give him sysAdmin rights. If I just leave the login at public I cannot login, but as soon as I give him sysAdmin rights he can. Even if I give him database owner rights, he still cannot login...What am I missing?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-07 : 18:20:52
|
| SSMSthe error is Login failed for User... Error 18456 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-07 : 20:20:45
|
| From the Sql Error Log...Login failed for 'domain\user'. [CLIENT: 192.168.0.77]Error: 18456, Severity: 14, State: 11. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-07 : 20:44:58
|
| pretty much the same errorCannot connect to <instance>Login failed for user '<domain\user>'. (Microsoft SQL Server, Error: 18456) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-08 : 09:05:53
|
| That did it...Sorry for the hassle...1 more (hopefully quick) question...how can I give a user "almost" sysadmin user rights...There are a few stored procedures I don't want him to have access to...I believe that if I give him sysadmin it does not check any other rights... Correct?So would I have to set up a new database role and grant rights to every object? is there a quick way to do that?Thanks again, you saved me a lot of time... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-08 : 14:54:13
|
| Thank you...I appreciate all your help... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
BButler59
Starting Member
7 Posts |
Posted - 2010-10-14 : 12:24:40
|
quote:
Originally posted by tkizer
Give him db_owner on the database and then deny the stored procedures you don't want him to have access to. Or use something like this (isp_grant_permissions): http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=3486Tara Kizer
Tara,Another question...Can't he change permissions if he is the owner of the database?Thanks... |
|
|
|
|
Login Problem