August 5, 2011 at 8:02 am
Hello,
My DBA left recently and I have been temporarily filling in for some database stuff. I have a SQL login user who is unable to access the database via SQL Server Management studio. That same login is on a different database was setup as serveradmin role.
When I went to Security > Logins > User, right-click > properties and went to Server roles and checked 'serveradmin' however it is still unable to login via Management studio. I found this script to run on a database:
SELECT
UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, LoginType = sp.type_desc
FROM sys.database_principals dp
JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
When it runs, it still says the role for that particular user is 'setupadmin'.
How do I fix this? I've searched the web but so far have come up empty handed. Thanks
August 5, 2011 at 8:25 am
It might be an issue with having previously been denied permissions.
Explicit grants will fix that.
Alternatively, drop and re-create the login with the permissions you need it to have.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 5, 2011 at 8:34 am
Thanks, but I'm a system admin, so my SQL knowledge is limited. Is this done in the 'securables' section?
GSquared (8/5/2011)
It might be an issue with having previously been denied permissions.Explicit grants will fix that.
Alternatively, drop and re-create the login with the permissions you need it to have.
August 5, 2011 at 8:41 am
nocode99 (8/5/2011)
Thanks, but I'm a system admin, so my SQL knowledge is limited. Is this done in the 'securables' section?GSquared (8/5/2011)
It might be an issue with having previously been denied permissions.Explicit grants will fix that.
Alternatively, drop and re-create the login with the permissions you need it to have.
Try this:
EXEC master..sp_addsrvrolemember @loginame = 'yourlogin', @rolename = 'serveradmin'
Or
In Security folder for the server double click on a login and in the server roles page select serveradmin.
August 5, 2011 at 8:54 am
Thanks, but that didn't seem to work. The command executed fine, but when I check the settings by running the script in my previous post, it still shows it as 'setupadmin'.
Should i do the drop/recreate the other poster mentioned? If so, what is the best way to handle that.
AlexSQLForums (8/5/2011)
nocode99 (8/5/2011)
Thanks, but I'm a system admin, so my SQL knowledge is limited. Is this done in the 'securables' section?GSquared (8/5/2011)
It might be an issue with having previously been denied permissions.Explicit grants will fix that.
Alternatively, drop and re-create the login with the permissions you need it to have.
Try this:
EXEC master..sp_addsrvrolemember @loginame = 'yourlogin', @rolename = 'serveradmin'
Or
In Security folder for the server double click on a login and in the server roles page select serveradmin.
August 5, 2011 at 9:52 am
Right click on Login >>>Delete.
Right click on Security >>>New>>>Login...Uncheck "User must change password at next login"
Choose authentication mode windows or SQL.
Select Default Database
Check serveradmin from Server Roles.
Click OK
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply