Permissions to a local account on which SQL Server service works

My SQL Server 2005 runs on a local account. Is it neccesary to assign this login in SQL to a System Administrator role?

And is there any difference in SQL Server 2000?

thanks

Przemo

If you have used SQL Server Configuration Manager to set the service account, you don't need to do anything. The same thing goes for SQL Server 2000, if you have used Enterprise Manager to change the service account. You should avoid changing the service account manually and use instead Configuration/Enterprise Manager.

Thanks
Laurentiu

sql

Permissions on stored procedures

Using Server Management Studio Express and SQL Server 2005 Express - is it possible to assign Exec permissions for users on a sproc by sproc basis. If so, how do I do this?

TIA

--
Mike BrindOk - I've found out how to do it. Just run a GRANT command in the Query Pane on the chosen procedure for the selected user.
sql

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
StephenStephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/community/newsgroups/server/sql.mspx
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
Stephen
Stephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/com...erver/sql.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com

permissions not 'sticking' with Yukon June CTP?

Hi,
I'm trying to assing permissions using the MS SQL Management studio on the
Yukon June CTP, but every time i assign permissions and press 'OK' and go
back to the permissions screen, it is gone. I am new to MSSQL -- do i need
to run a FLUSH PRIVELEGES or some equivalent command?
Thanks,
StephenStephen wrote:
> Hi,
> I'm trying to assing permissions using the MS SQL Management studio
> on the Yukon June CTP, but every time i assign permissions and press
> 'OK' and go back to the permissions screen, it is gone. I am new to
> MSSQL -- do i need to run a FLUSH PRIVELEGES or some equivalent
> command?
> Thanks,
> Stephen
Please post SQL Server 2005 issues in the 2005 newsgroups. These
newsgroups are currently for versions other than 2005.
http://www.microsoft.com/technet/co...server/sql.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com

Permissions not effective for Windows Authentication login

Hello All,

I'm hoping someone can help me with this puzzle.

Most logins I've created have been SQL Server authenticated. I assign the login newEmployee to a role existingRole, and ensure the role has the required permissions. This didn't seem to be rocket science....

My company has been provided with an application with a SQL Server back-end. My instructions were to create a Windows authenticated login and give it full access to the database. I followed the above principles, but running the application, the user got the error -

SELECT permission denied on object 'sysobjects', database 'databasename', owner 'dbo'.

So I decided to try the simplest possible scenario to make it work:

I've created a login DOMAIN\newEmployee with Windows authentication.

DOMAIN\newEmployee has been granted access to databasename.

By default, DOMAIN\newEmployee is a member of Public.

Public has been granted all available permissions on all objects.

ie... grant all on userTables to public

........grant all on sysobjects to public

........grant all on otherSystemTables to public

etc.

Running the application, the user still gets the above error. I'd send the problem back to the vendor, except if I've logged onto the PC as DOMAIN\newEmployee, querying -

select * from dbo.sysobjects

via Query Analyser produces the same error message. (An equivalent error message is produced when querying a user-created table).

To compare, I then created a login newEmployee2 with SQL Server authentication.

newEmployee2 has been granted access to databasename.

select * from dbo.sysobjects

runs successfully from Query Analyser (as to any queries on user-created tables).

What else is required to grant access to tables from a Windows authenticated login?

( What really scares me, is that the application will run if I make the Windows authenticated login a member of server roles System Administrator and Database Creators, then the application will run - but I don't want this to be the permanent solution. Even after doing this, the above query still fails in Query Analyser for that login, suggesting that there is something wrong with how I configured the permissions. )

Any help would be appreciated.

Thanks.

Kim.

Moved to Security.|||

Let me see if I understand the scenario, please correct me if I am missing something:

· DOMAIN\newEmployee & DOMAIN\newEmploee2 are Windows domain users

· DOMAIN\newEmployee is a member of existingRole

· public has been granted the permissions you mentioned.

· DOMAIN\newEmploee2 can select from dbo.sysobjects

· DOMAIN\newEmploee cannot select from dbo.sysobjects and gets back a permission denied error

From your description, it seems like the most likely cause is that newEmployee has an explicit denied permission on dbo.sysobjects either directly or via a role membership. Check the permissions for all the roles and groups that newEmployee is a member of.

BTW. Some of the objects and permissions that you mentioned here are deprecated, they will still work on SQL Server 2005, but they are supported only for backwards compatibility.

Let us know if this information was useful or/and if you have further question, please also let us know what version of SQL Server you are using in order to better assist you.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi Raul,

D'oh !! I've checked db_denydatareader instead of db_datawriter.

I'll now crawl into a hole and die of embarrassment.

Thanks for the help.

Kim.

Permission to execute fn_get_sql

Dear All,

I want to assign privelege to execute the following in SQL Server 2000 SP4 + 2187

select * from ::fn_get_sql(0x01000700AC38820138A1786C0400000000000000)

I have tried the following:-

use master

go

grant select on fn_get_sql to <user>

I am getting error:

Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'fn_get_sql'.

I believe that ::fn_get_sql is an administrative use only function, and that permission is limited to either sysadmin or db_databaseowner. (I think the former...)

|||

You require VIEW SERVER STATE permission to use it. Please refer to BOL (http://msdn2.microsoft.com/en-us/library/ms189451.aspx) for detailed information.

-Raul Garcia

SDE/T

SQL Server Engine

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can[vbcol=seagreen]
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.[vbcol=seagreen]
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
Quentin
Although you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>
|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
>
|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
the[vbcol=seagreen]
permissions[vbcol=seagreen]
can
>
|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
tables.[vbcol=seagreen]
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
tables.
> the
> permissions
> can
>

permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>