September 8, 2009 at 2:40 pm
I have a DB server in which i will be adding/dropping users very frequently as per the requirement. Now i would like to implemet this from a stored proc in which it has to do the follwing.
1. List users in a DB
2. Add user to the DB with one of the Db role
3. Remove any users if required
4. Run a script to deny permissions on few tables in the db.
I am not sure if this is possible from a store proc but would really like to do so, I understand it involves has admin previlages but i thougt of creating a sql user with admin access to run this proc.
September 9, 2009 at 4:38 am
Hello,
Yes, you can do this from a stored procedure. Will require a bit of dynamic SQL, but it can be done.
Don't quite understand what you mean in point 4, but the rest of it will be fine!
September 9, 2009 at 7:00 am
I have a script which i want to run from the same stored procedure which locks some tables in the database which contians sensitive data.
Can you let me know how that can be done from stored procedure
September 9, 2009 at 7:06 am
Also I would like to know if there are any dis advantages by managing security from a web page out of sql server.
September 9, 2009 at 7:26 am
Tara (9/9/2009)
I have a script which i want to run from the same stored procedure which locks some tables in the database which contians sensitive data.Can you let me know how that can be done from stored procedure
Not sure I understand what you mean by locking the data, you could always deny select permissions on those tables
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 9, 2009 at 7:33 am
Tara (9/9/2009)
I have a script which i want to run from the same stored procedure which locks some tables in the database which contians sensitive data.Can you let me know how that can be done from stored procedure
Do you mean you only want certain users to be able to read from the tables with sensitive data?
You can also restrict who can access a certain table or you could use a view to present data differently (for example, exclude a salary column).
September 9, 2009 at 7:52 am
yes that is what my script does in the 4th part for which i am not worried but i would like to know how can i implement forst 3 steps from a stored procedure.
Once if i can implement this i can run my script in the last step which will deny permissions on some tables.
September 9, 2009 at 8:01 am
Tara (9/9/2009)
yes that is what my script does in the 4th part for which i am not worried but i would like to know how can i implement forst 3 steps from a stored procedure.Once if i can implement this i can run my script in the last step which will deny permissions on some tables.
You'll need to use some dynamic sql to do this. A very quick example:
create procedure adduser
@user varchar(50), @db varchar(20)
as
declare @sql nvarchar(200)
select @sql = N'use ' + @db + ' create user ' + @user + ' from login ' + @user + ' with default_schema = dbo'
exec (@sql)
go
adduser 'testuser', 'testdatabase'
For the other steps, you'll have to use the "drop user" and "sp_addrolemember"
You'll need to add error checking etc...but this should give you enough to get you started.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply