June 28, 2012 at 6:25 am
Hi All,
I am doing login downgrade in My Server...
this is my Script It is Throwing Error. Please Let me know if you have any ideas..
USE [DB_Name]
IF EXISTS (SELECT * FROM sys.database_principals where name='Username')
BEGIN
EXEC SP_DROPROLEMEMBER 'DB_OWNER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
ELSE
BEGIN
CREATE USER UserName FOR LOGIN UserLogin;
GRANT CONNECT TO UserName
GRANT EXECUTE TO UserName
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
June 28, 2012 at 6:30 am
SqlSpider... (6/28/2012)
Hi All,I am doing login downgrade in My Server...
this is my Script It is Throwing Error. Please Let me know if you have any ideas..
USE [DB_Name]
IF EXISTS (SELECT * FROM sys.database_principals where name='Username')
BEGIN
EXEC SP_DROPROLEMEMBER 'DB_OWNER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
ELSE
BEGIN
CREATE USER UserName FOR LOGIN UserLogin;
GRANT CONNECT TO UserName
GRANT EXECUTE TO UserName
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
Mind sharing the error msg?
June 28, 2012 at 6:35 am
clayman (6/28/2012)
SqlSpider... (6/28/2012)
Hi All,I am doing login downgrade in My Server...
this is my Script It is Throwing Error. Please Let me know if you have any ideas..
USE [DB_Name]
IF EXISTS (SELECT * FROM sys.database_principals where name='Username')
BEGIN
EXEC SP_DROPROLEMEMBER 'DB_OWNER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
ELSE
BEGIN
CREATE USER UserName FOR LOGIN UserLogin;
GRANT CONNECT TO UserName
GRANT EXECUTE TO UserName
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
Mind sharing the error msg?
This is Error I am Getting
Msg 15063, Level 16, State 1, Line 16
The login already has an account under a different user name.
June 28, 2012 at 6:43 am
SqlSpider... (6/28/2012)
clayman (6/28/2012)
SqlSpider... (6/28/2012)
Hi All,I am doing login downgrade in My Server...
this is my Script It is Throwing Error. Please Let me know if you have any ideas..
USE [DB_Name]
IF EXISTS (SELECT * FROM sys.database_principals where name='Username')
BEGIN
EXEC SP_DROPROLEMEMBER 'DB_OWNER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
ELSE
BEGIN
CREATE USER UserName FOR LOGIN UserLogin;
GRANT CONNECT TO UserName
GRANT EXECUTE TO UserName
EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'UserName'
EXEC SP_ADDROLEMEMBER 'DB_DATAWRITER' , 'UserName'
END
Mind sharing the error msg?
This is Error I am Getting
Msg 15063, Level 16, State 1, Line 16
The login already has an account under a different user name.
Ok, then double check that the user in your db have not been mapped to the login yet
Edit
Here's a quick way
SELECT log.loginname ,
usr.name AS username
FROM sysusers usr
INNER JOIN master..syslogins log ON usr.sid = log.sid
WHERE usr.name = 'UserName'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply