User Management

Question

Post reply

User Management

A+R

SSC-Addicted

Points: 423
More actions
September 28, 2005 at 2:41 am

#90012

Hi All,
I've to implement Security in already created database in SQL Server 2000 and the users connect with sa (as no restriction in our comapny for users earlier).
Now as a DBA, i want to use this feature for Domain Users, for which i need to Create Roles and Persmissions of Existing Objects.
Do any one have such helping material or link?
Which Query is used to assign Specific Permission for Roles? as i want to give access/Permission to a specific role on all objects in the current database.
e.g.
Grant SELECT ON [NewRole] TO [DomainUser]
Can i do this or Correct It?
Waiting for ur FeedBacks!
Note: i won't used system roles (until necessary) just make User Defined Roles.
---------
Regards,
Ali Raza

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 1

exec sp_addrole '[ Your role name]

'

GRANT SELECT ON [Your table or view] TO [YourRole]

GRANT EXECUTE ON [Your stored procedure] TO [YourRole]

EXEC sp_addrolemember '[ Your role name]','[Your user]'

Don't grant access directly to a user otherwise when that user leaves you will have to reassign all the permissions to another user.

By using roles you simply add or remove users to the role as and when you need them.

A+R SSC-Addicted Points: 423 More actions · Answer 2

Thanks David Poole

But My Point is this, that i want to give Access to a single Role of a Particular User on Multiple Objects. Say i have 20 Tables and 100 Plus Sps and some User defined Functions in a database And Now i create a Role [YourRole] and Now Give it the SELECT Permission on all Objects then i'll add that role for a user so that user will access all objects.

GRANT SELECT ON [Your table or view] TO [YourRole]

GRANT EXECUTE ON [Your stored procedure] TO [YourRole]

These queries gave access of a single table etc (Right) but is any way possible to Grant [YourRole] on all Objects

LIKE THIS

GRANT ALL TO [YourRole]

But this will Grant all other permission i just give SELECT,

So the Query like this

GRANT SELECT TO [YourRole]

it'll generate an error, but i'll to do something like this.

Hope u'll understand my question Now.

Wait for ur & Others feedback.

---------
Regards,

Ali Raza

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 3

DECLARE @sTableName SYSName

SET @sTableName=''

WHILE @sTableName IS NOT NULL

BEGIN

SELECT @sTableName = MIN(Name) FROM SysObjects WHERE Type='U' AND Name>@sTableName

IF @sTableName IS NOT NULL

EXECUTE ('GRANT ALL ON ' + @sTableName + ' TO YourRole')

END

Carl Federl One Orange Chip Points: 25384 More actions · Answer 4

Alternatively, sp_execresultset is a system procedure that executes the result set of a SQL statement.

exec dbo.sp_execresultset @cmd =

'select ''GRANT ALL ON ''

+ QUOTENAME( table_schema) + ''.'' + QUOTENAME ( table_name )

+ '' TO YourRole''

from information_schema.tables'

SQL = Scarcely Qualifies as a Language

A+R SSC-Addicted Points: 423 More actions · Answer 5

Thanks David Poole and Carl Federl

I’ve checked both the solutions and both fulfilled what I want?

I can also do this with Cursor.

---------
Regards,

Ali Raza

David Poole-249495 Ten Centuries Points: 1106 More actions · Answer 6

My version doesn't use cursors. I tend to view cursors as an object of last resort because of the locking issues.

If you want to affect all views as well then change the line

SELECT @sTableName = MIN(Name) FROM SysObjects WHERE Type='U' AND Name>@sTableName

to

SELECT @sTableName = MIN(Name) FROM SysObjects WHERE Type IN('U','V') AND Name>@sTableName