Solved

how to grant sysadmin to a user?

Posted on 2010-08-23
23
3,423 Views
Last Modified: 2012-05-10
Hi,
I want to know how to grant  sysadmin right to a user by sqlcmd?
and how to grant a full right of database (fsze88) to a user by sqlcmd?

Thanks
0
Comment
Question by:fsze88
23 Comments
 
LVL 11

Expert Comment

by:aelliso3
ID: 33501079
exec sp _addsrvrolemember N'fsze88', sysadmin
0
 
LVL 15

Author Comment

by:fsze88
ID: 33501306
how about

grant a full right of database (fsze88) to a user by sqlcmd?
0
 
LVL 3

Expert Comment

by:Dave_LaSalle
ID: 33501354
Is this what you are looking for?
http://msdn.microsoft.com/en-us/library/ms189595.aspx
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 15

Author Comment

by:fsze88
ID: 33501422
I means a database named 'fsze88'
and I would like to know how to grant full right of database 'fsze88' to a user 'fsze88'
Untitled.png
0
 
LVL 7

Expert Comment

by:wittyslogan
ID: 33501496
EXEC fsze88..sp_addrolemember 'db_Owner', 'UserName'
0
 
LVL 5

Expert Comment

by:TIGERMARK
ID: 33501533
http://www.adriancolquhoun.com/WindowsWorkflow/UsingtheSqlWorkflowPersistenceService/tabid/138/Default.aspx

Simply create a new Database Project under Other Project Types, creating a reference to the target database.  From there it is easy to right-click and add existing .sql files.  Then just right-click and run.
0
 
LVL 15

Author Comment

by:fsze88
ID: 33501598
1> EXEC fsze88..sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33501729
EXEC sp_addrolemember 'db_Owner', 'domain\fsze88'
or
EXEC sp_addrolemember 'db_Owner', 'fsze88'
We have a little bit of confusion. SYSADMIN is a server role and not a database role. It sounds like you are looking for db_owner permission on db fsze88
0
 
LVL 15

Author Comment

by:fsze88
ID: 33505137
I don't know why it said no this user (fsze88)


C:\Users\fsze88>sqlcmd
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1> EXEC sp_addrolemember 'db_Owner', 'domain\fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'domain\fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33505499
Is this ID an NT Authentication, or SQL auth?
0
 
LVL 15

Author Comment

by:fsze88
ID: 33507355
should be SQL auth
Untitled.png
0
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 500 total points
ID: 33510576
after you started SQLCMD, did you go into the database you are trying to grant permissions too:
 

USE fsze88
GO

EXEC sp_addrolemember 'db_Owner', 'fsze88'
GO 

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33510822
sorry same

C:\Users\fsze88>sqlcmd
1> use fsze88
2> go
¿¿¿¿¿¿¿¿¿¿ 'fsze88'¿
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> GO
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1>

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33511099
start off with
SQLCMD -S FSZE88-PC
the error, Msg 15410, is saying that the user does not exist, which is strange since you logged in with that user ... hopefully with stating the server name in the command above it will direct it to the correct place.
0
 
LVL 15

Author Comment

by:fsze88
ID: 33511340
very strange

same
C:\Users\fsze88>SQLCMD -S FSZE88-PC
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line
 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿
1> use fsze88
2> go
¿¿¿¿¿¿¿¿¿¿ 'fsze88'¿
1> EXEC sp_addrolemember 'db_Owner', 'fsze88'
2> go
Msg 15410, Level 11, State 1, Server FSZE88-PC, Procedure sp_addrolemember, Line 75
¿¿¿¿¿¿ 'fsze88' ¿¿¿¿¿¿¿¿¿¿¿¿

Open in new window

0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33511743
Can you try the following to see if that login is listed ... start SQLCMD and then use the statement below.
SELECT name, sid, type, type_desc FROM sys.server_principals
   WHERE type = 'S';

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33511834
Thanks
1> SELECT name, sid, type, type_desc FROM sys.server_principals
2>    WHERE type = 'S';
3> go
name                                                                                                                             sid
                 type type_desc
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------
---------------- ---- ------------------------------------------------------------
sa                                                                                                                               0x01
                 S    SQL_LOGIN
##MS_PolicyEventProcessingLogin##                                                                                                0x0A6983CDF023464B9E86E4EEAB92C5DA
                 S    SQL_LOGIN
##MS_PolicyTsqlExecutionLogin##                                                                                                  0x8F651FE8547A4644A0C06CA83723A876
                 S    SQL_LOGIN
fsze88                                                                                                                           0xE57DD7E18C89D440AB4942AD213F64A2
                 S    SQL_LOGIN

(4 rows affected)
1>

Open in new window

0
 
LVL 15

Author Comment

by:fsze88
ID: 33565875
anyone interested in follow UP?
Thanks!
0
 
LVL 15

Author Comment

by:fsze88
ID: 33651960
no more reply?
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33652022
Sry for leaving you hanging on this one. Let me take a look in the morning and we'll see if we can come up with an answer for it...
0
 
LVL 11

Expert Comment

by:aelliso3
ID: 33652023
Sry for leaving you hanging on this one. Let me take a look in the morning and we'll see if we can come up with an answer for it...
0
 
LVL 15

Author Comment

by:fsze88
ID: 33652048
sure
0
 
LVL 11

Accepted Solution

by:
aelliso3 earned 500 total points
ID: 33654679
This should do it ..
 

USE fsze88
GO

EXEC sp_changedbowner 'fsze88' 
GO

Open in new window

0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL: Do I need CLUSTERED here? 13 44
Passing Parameter to Stored Procedure 4 24
SQL Query assistance 16 26
Proper Case SQL Command 2 9
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 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