How do I: Create a user with anothers permissions

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

SQL Server 2000 Forums

SQL Server Administration (2000)

How do I: Create a user with anothers permissions

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2003-09-02 : 16:19:18

Ok. Maybe Microsoft is lacking in this area or maybe not. I have some trusted logins setup and now an entire department has moved over from an outside network into ours. Welp, thats fine and dandy cause the net admin handled all of that good stuff. In any case, I know that in Oracle you have the ability to create a new login based from another user. The newly created login would then have all permissions granted to it as the one you based it off of. I dont really remember the syntax and it doesnt really apply being that I have SQL2k. Is there a feature in SQL2k that is similar to the Oracle method?

Thanks in advance,
Daniel

Daniel
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-02 : 16:27:15

SQL Server does not have this option. That is why roles should be used for permissions. If you use a role, you only need to grant permissions to the role and then add the users to the role. Then all users in the role have the permissions that the role has. A database can have many roles and users can be a member of many roles.

Tara

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2003-09-02 : 16:42:03

quote:
Originally posted by tduggan

SQL Server does not have this option. That is why roles should be used for permissions. If you use a role, you only need to grant permissions to the role and then add the users to the role. Then all users in the role have the permissions that the role has. A database can have many roles and users can be a member of many roles.

Tara

Yeah. I havent really used roles here because we dont use standard logins. Everything is based off of NT Authentication with NT groups so it leaves it all on the net admin to add/remove new or old employees, which has to be done anyway (no sence in making extra work)

. It makes my life much easier to setup one department, give them the necessary rights, knock out a report, and then never worry about it again (*Until now*). Well I guess the better way to set that up would be to use the roles and do as you say and add the NT Groups to those roles. This would give the layer of separation that I need for this type of problem.

Interesting...

Daniel
SQL Server DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-02 : 16:51:16

Even if you are using NT authentication, you should still use roles.

Here is some code that will help you fix your problem though (will work for TABLES only):



SELECT 'GRANT ' + PRIVILEGE_TYPE + ' ON ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' TO SomeNewUser'
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE = 'SomeUser'

Just change SomeUser to the account that already exists and change SomeNewUser to the new account. Then run the query, copy the code from the output window into a new window, then execute this code.

Here is some code for stored procedures:


SELECT 'GRANT EXEC ON ' + o.name + ' TO SomeNewUser'
FROM syspermissions p 
INNER JOIN sysobjects o ON p.id = o.id
INNER JOIN sysusers u ON p.grantee = u.uid
WHERE o.type = 'P' and USER_NAME(grantee) = 'SomeUser'

If you've got other types of objects to grant permissions for this user, just modify the last code segment to what you need (o.type would need to be changed and possibly the permission being set).

Tara

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2003-09-02 : 17:12:14

quote:
Originally posted by tduggan

Even if you are using NT authentication, you should still use roles.

Hmm.. Thats cool I'll check these queries out. As I was saying it hasnt ever been a problem for me until the entire department decided to move internally into our company and now their logins are on our domain. As I also said it makes complete sence to separate the permissions from the logins. By using the NT Groups as I have been and then assigning them to roles where the permissions are I still get the same result as far as no increase in workload by adding/removing users from the roles daily but I also get an easier to manage setup in the event that something like this should occur. Its a good call. Thanks for the help.

Thats why I love SQL Team... When you need quick answers, you get them.

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources