Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » Security (SS2K8) » how to create a sql login and add to database...
Add to briefcase

how to create a sql login and add to database user which already exists Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
naga.rohitkumar
Posted Friday, November 02, 2012 12:09 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 525, Visits: 997

hi everyone ,

how to create a sql login and add to database user which already exists
1. user "appp" is already exists in the datasbase "real" which is restored from old backup after fresh sql server installation
2. now we want to create the login "appp" [with sqlserver authnetication ] and give access to the database "real" with server role = bulkadmin and database role =public

can any one give script for this ????

Thanks & Regards
NAGA.ROHITKUMAR
Post #1380152
anthony.green
Posted Friday, November 02, 2012 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831
Create the login using CREATE LOGIN

Then use ALTER USER in the database in question to re-map the user to the login

Then use sp_addrolemember / sp_addsrvrolemember / sp_droprolemember / sp_dropsrvrolememeber to add remove the login/user to the nessesary DB or server level roles


Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1380192
naga.rohitkumar
Posted Monday, November 05, 2012 4:25 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:34 AM
Points: 525, Visits: 997
if u dont mind can u expalin with detaled script

Thanks & Regards
NAGA.ROHITKUMAR
Post #1380970
jeetsingh.cs
Posted Monday, November 05, 2012 4:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380
IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name ='USerName')
DROP USER [USerName]
CREATE USER [USerName] FOR LOGIN [LoginName]
EXEC sp_addrolemember 'db_datareader','USerName'

You can also refer MSDN forums for user permissons
http://www.mssqltips.com
Post #1380981
Lowell
Posted Monday, November 05, 2012 6:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 11,605, Visits: 27,646 
   CREATE LOGIN [appp] 
     WITH PASSWORD=N'NotTheRealPassword' 
     MUST_CHANGE, 
     DEFAULT_DATABASE=[master], 
     CHECK_EXPIRATION=ON, 
     CHECK_POLICY=ON; 
    USE real;
GO
    ALTER USER [appp] WITH LOGIN = [appp];

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1381044
Bhuvnesh
Posted Monday, November 12, 2012 2:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451
a new login will have different SID from the existing user "appp" So to
Re-maps that user to new login by changing the user's Security Identifier (SID) to match the login's SID. which is done by ALTER USER command

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1383555
Hemant.R
Posted Tuesday, December 11, 2012 12:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:29 AM
Points: 45, Visits: 171
can plz tell , how will u change sid of particular login
Post #1394919
anthony.green
Posted Tuesday, December 11, 2012 1:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831
Google sp_help_revlogin, it will genereate the create script with the particular SID, then you just need to run it on your secondary server.


Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger
Post #1394937
Perry Whittle
Posted Tuesday, December 11, 2012 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 5,201, Visits: 11,150
It's deprecated but still usable in SQL Server 2008 R2 and it's a one liner. As follows

USE [real]
GO
EXEC sys.sp_change_users_login 'Auto_Fix', 'appp', NULL, 'somepassword'
GO

Then just use 

EXEC sp_addsrvrolemember 'appp', 'bulkadmin'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1395065
Hemant.R
Posted Tuesday, December 11, 2012 9:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:29 AM
Points: 45, Visits: 171
you can also use, in case u want give dbowner role


use database name

sp_changedbowner 'login name'
Post #1395188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.