May 22, 2006 at 2:09 pm
I am working on a custom role that will allow a security application access to our SQL 2000 environments. Here is a small portion of my code.
Use Master
EXEC sp_addlogin 'login', 'pwd', 'master'
EXEC sp_grantdbaccess 'login', 'login'
EXEC sp_addrole 'ROLE'
EXEC sp_addrolemember 'ROLE', 'login'
GRANT SELECT
ON syslogins
TO ROLE
sp_grantdbaccess adds the login to the database with the rights of the public role. The public role has an explicit deny select on the password column of master.dbo.syslogins. My new role does not override the explicit column denial, and I can not remove the login from the public role.
Is there a way for me to allow this application login to have select permissions on the password column of master.dbo.syslogins WITHOUT modifying the permissions of the public role, or using a server role?
I am shooting for the absolute minimum permissions in this context. Thanks
Cody Pack
MCSE Windows 2000
MCDBA SQL Server 2000
May 23, 2006 at 4:34 am
as far as I remember from doing a similar execise ( many years ago ) the column has distinct rights for sysadmin/securityadmin only. Therefore you must alter the column permissions for your new role. You might find using stored procedures easier.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 23, 2006 at 4:36 am
sorry - should have also added that it's not a good idea to mess with system tables and such - can open a can of worms, you should be aware that sp's may change system tables/procs without any warnings and thus break either sql server or anything you have written.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply