?
Solved

how to grant sysadmin to a user?

Posted on 2010-08-23
23
Medium Priority
?
3,587 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 2000 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 2000 total points
ID: 33654679
This should do it ..
 

USE fsze88
GO

EXEC sp_changedbowner 'fsze88' 
GO

Open in new window

0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Here is how to restore SQL Server database to the point in time.  Follow the step by step approach to restore your database at a specific point in time and also understand its alternate approach.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

589 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