Solved

How to create a user with the same exact SA-equivalency in Microsoft SQL 2008 and 2014

Posted on 2016-11-06
12
28 Views
Last Modified: 2016-11-07
Pleases advice on the steps to create a user with the same privileges as SA.  We need for an application to have its own login separate from any real user and that login then needs to have SA-equivalency.
0
Comment
Question by:rayluvs
  • 6
  • 6
12 Comments
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876172
Try this..

CREATE LOGIN [testPawan] WITH PASSWORD=N'Pawan', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

GRANT CONTROL SERVER TO [testPawan] WITH GRANT OPTION;

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41876190
Thanx!

How can we validate to our boss the new user has the same privileges as SA?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876201
Use this ... run below queries and check

Supply NewLoginName in the query..

--
SELECT u.name, r.name
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id
  WHERE u.name = 'NewLoginName';
--

Open in new window

--
--
SELECT u.name, r.name
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id
  WHERE u.name = 'sa';
--

Open in new window


Hope it helps  !!
0
 

Author Comment

by:rayluvs
ID: 41876260
Thanx will do right away!
0
 

Author Comment

by:rayluvs
ID: 41876274
results in 0 rows, both of them.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876522
Try this and see what records you get

SELECT u.name, r.name
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id

--

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:rayluvs
ID: 41876535
It returns:

name name
---- ---------
dbo  db_owner

Open in new window


Don't understand the results; how do I explain my boss with this validation that the new user has the same privileges as SA?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876583
Have you created the new login, I am getting below- :)

name                        name
dbo                              db_owner
PawanKumar        db_owner
0
 

Author Comment

by:rayluvs
ID: 41876746
Yes.

Corerct us if we are wrong, but are you saying that if the user created is db_owner, that username has the same exact equivalency in Microsoft SQL as the SA account? (please explain)
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41876751
Try this updated... your user should have sysadmin role.

CREATE LOGIN [LoginSQLSameAsSA] 
WITH PASSWORD = 0x0200C612A516C8C41A5C6F642848AB8E3B809EB6917F277AF4C04951065FBF03FDF7F6364F1AF56D4C846FE2593F922865FF0D99C6D91D09A411F1F28FD368B2798C8F968149
HASHED, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

EXEC master..sp_addsrvrolemember @loginame = N'LoginSQLSameAsSA', @rolename = N'sysadmin'

Open in new window


Query ...


--

SELECT sr.name RoleName, sl.name UserName , sl.type_desc , sl.default_database_name FROM master.sys.server_role_members SRM
JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE sl.name in ( 'sa' , 'LoginSQLSameAsSA' )

--

Open in new window


Output --

RoleName      UserName                                type_desc        default_database_name
sysadmin      sa                                                SQL_LOGIN        master
sysadmin      LoginSQLSameAsSA                SQL_LOGIN        master

Hope it helps!
0
 

Author Comment

by:rayluvs
ID: 41876765
Excellent Thanx!!!  By showing our boss that the new sa user is in RoleName       we think is more than sufficient (we were printing out different screens in SSMS to how our boss, thanx for the help!).
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41876769
Welcome rayluvs !!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now