Permission issue on user with db_reader

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 2008 Forums

Transact-SQL (2008)

Permission issue on user with db_reader

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-30 : 11:46:00

Dear All,

I try to run SP with

IDENTITY_INSERT DB.dbo.tblA ON
:
:

IDENTITY_INSERT DB.dbo.tblA OFF

i already added,
SET @SQLString = N'GRANT db_ddladmin ON dbo.eAsset TO '+@User+'';
EXEC (@SQLString);

IDENTITY_INSERT DB.dbo.tblA ON
:
:

IDENTITY_INSERT DB.dbo.tblA OFF

SET @SQLString = N'DENY db_ddladmin ON dbo.eAsset TO '+@User+'';
EXEC (@SQLString);

But still not work. Anyone have idea on how to do it using SP.

Thank You.

Regards,
Micheale

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-30 : 12:25:00

Perhaps create an SP_EXEC user that has all the permissions you need, then use that.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-30 : 19:23:13

I'm confused. "Db_ddladmin" is a Role; not a right to be granted. I would expect your script to throw a syntax error. Instead, I think you want to use the sp_addrolemember system stored procedure to add your user to the desired Role.[CODE]exec sp_addrolemember @rolename = N'db_dlladmin', @membername = @User;[/CODE]This has the additional advantage that you no longer need to use dynamic SQL.
HTH

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-31 : 09:31:08

I found a solution by using

EXEC sp_grantdbaccess 'Corporate\JeffL', 'UserName with Owned Schema & Role as Member: db_owner tick';

:
:

sp_revokedbaccess 'Corporate\JeffL'

Revote mean If the user should no longer have access to the database you can execute the sp_revokedbaccess stored procedure to remove the user.

But i looking for deny the user as dbaccess. Anyone know how to do it?

Please Advise.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources