April 5, 2016 at 12:14 am
Hello Team,
Any one have dynamic script to grant permissions to specific table or stored proc or view or schema.
I am planning to create one stored proc, which I will execute as per request comes. For example some request many only require access to table. Some may require access to schema only.
Please guide me.
Sagar Sonawane
** Every DBA has his day!!:cool:
April 5, 2016 at 1:31 am
Granting permissions on a database object is as simple as this:
GRANT <some permission> ON <some object> TO <some user>
What do you mean with "dynamic script"?
-- Gianluca Sartori
April 5, 2016 at 9:34 pm
Dynamic Script means , In only one stored procedure I can grant access to required object. It may be Table, schema,view or stored procedure.
Steps
1. It will verify if login exists else it will create login.
2. verify if user exists in db else it will create user.
3. Pass database name.
3. Input to sp : access to schema, table, view or stored proc (these 4 will be options)
4. Level of access (select , alter, update, execute, delete, insert).
5. Print message that User created and ABC level access granted to DBO schema for user xyz in database PQR.
I think now it is more clear.
Sagar Sonawane
** Every DBA has his day!!:cool:
April 6, 2016 at 1:52 am
1. It will verify if login exists else it will create login.
2. verify if user exists in db else it will create user.
3. Pass database name.
3. Input to sp : access to schema, table, view or stored proc (these 4 will be options)
4. Level of access (select , alter, update, execute, delete, insert).
5. Print message that User created and ABC level access granted to DBO schema for user xyz in database PQR.
1. Use the SUSER_ID(login name) function for this. Returns NULL if no matching login is found. To create a login use CREATE LOGIN xyz [FROM WINDOWS | WITH PASSWORD = *****]
2. USER_ID(user name) will do. Same as above. CREATE USER xyz FOR LOGIN xyz to create a user.
3. 4. Build a dynamic SQL string with GRANT <permission> ON <object> TO <user> and execute it
5. Print a message
It shouldn't be hard at all. If you need help with some specific step, we're here to help.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply